How does one put formatted date labels into excel with Putexcel?*

[Ibi Fakoya]

Hi there,

Here's my code:


local vars "date"

foreach pre in `vars'{
putexcel set "tabout/cleaningtest_`pre'.xls", replace
putexcel A1="`pre'" A3=("Sample/Patient Characteristic") B3=("Mean (SD)")
tab `pre', matrow(hnames)
local hlevels = rowsof(hnames)
forvalues i = 1/`hlevels' {
local hal`i' = hnames[`i',1]
local hal_lab`i' : label (`pre') `hal`i''
local x = `i' +2
excelcol `x'
putexcel `r(column)'3=("`hal_lab`i''"),

My aim is to put a series of dates in the colums in the excel spreadsheet. It works perfectly except it the dates are in numeric form and not formatted as they are displayed in Stata.

I am using Stata 14.2

Many thanks in advance.

[William Lisowski]

Perhaps adding the nformat option to your putexcel command to give Excel a format to apply to the numbers will do the trick. I don't think putexcel tries to deduce an appropriate Excel format from the Stata format.

[Ibi Fakoya]

Unfortunately, that doesn't work. But it's ok, I figured it out. This works:

local vars "date"

foreach pre in `vars'{
putexcel set "tabout/cleaningtest_`pre'.xls", replace
putexcel A1="`pre'" A3=("Sample/Patient Characteristic") B3=("Mean (SD)")
tab `pre', matrow(hnames)
local hlevels = rowsof(hnames)
forvalues i = 1/`hlevels' {
local hal`i' = hnames[`i',1]
local hal_lab`i' : label (`pre') `hal`i''
local hal_lab`i' : display %tmMon-CCYY `hal_lab`i''
local x = `i' +2
excelcol `x'
putexcel `r(column)'3=("`hal_lab`i''"),

Cheers!

[William Lisowski]

Post #3 tells us what post #1 did not - that your dates are SIF monthly dates rather than SIF daily dates. There is no equivalent in Excel, to the best of my knowledge. When I use month-and-year notation in Excel, Excel converts it to the first day of the month, and then formats that to not display the day. So the goal you set in post #1 is not attainable without converting the SIF monthly date to a SIF daily date. The output of help putexcel has further advice about exporting SIF dates to Excel.